package com.libweb.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.libweb.model.dto.Book;

public class BookDao {
	
	public void insertBook(Book book) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			//1. 드라이버 로딩
			Class.forName("com.mysql.jdbc.Driver");
			//2. 연결 생성
			conn = DriverManager.getConnection("jdbc:mysql://172.16.3.13:3306/libraryweb",	//url
					"instructor", "mysql");													//account
			
			
			//3. 명령 생성
			String sql = "INSERT INTO book "
					+ "(title, publisher, author, pubdate) "	//아이디 비밀번호 이름 주소 전화번호
					+ "VALUES (?, ?, ?, ?);";	
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, book.getTitle());
			pstmt.setString(2, book.getPublisher());
			pstmt.setString(3, book.getAuthor());
			pstmt.setDate(4, book.getPubDate());
			
			//4. 명령 실행
			pstmt.executeUpdate();
		}catch (Exception ex) {
			
		}finally {
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}		
	}
	
	public List<Book> getBooks() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Book> books = new ArrayList<Book>();
		
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT bookno, title, publisher, author, pubdate, status, regdate From book Order by bookno";
			pstmt = conn.prepareStatement(sql);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while(rs.next()) {
				Book book = new Book();
				book.setBookNo(rs.getInt(1));
				book.setTitle(rs.getString(2));
				book.setPublisher(rs.getString(3));
				book.setAuthor(rs.getString(4));
				book.setPubDate(rs.getDate(5));
				book.setStatus(rs.getBoolean(6));
				book.setRegDate(rs.getDate(7));
				
				books.add(book);
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return books;
	}
	
	public Book getBookByBookNo(int bookno) {
			
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Book book = null;
		
		try {
			//2. 연결 생성
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = "SELECT bookno, title, publisher, author, pubdate, status, regdate From book WHERE bookno = ? Order by bookno";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bookno);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				book = new Book();
				book.setBookNo(rs.getInt(1));
				book.setTitle(rs.getString(2));
				book.setPublisher(rs.getString(3));
				book.setAuthor(rs.getString(4));
				book.setPubDate(rs.getDate(5));
				book.setStatus(rs.getBoolean(6));
				book.setRegDate(rs.getDate(7));
			}
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try { rs.close(); } catch(Exception ex) {}
			try { pstmt.close(); } catch(Exception ex) {}
			try { conn.close(); } catch(Exception ex) {}
		}
		return book;
	}

	public void deleteBook(int bookNo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
						
			String sql = "DELETE FROM book WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,  bookNo);
			
			pstmt.executeUpdate();
						
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			//6. 연결닫기
			if (pstmt != null) try { pstmt.close(); } catch (Exception ex) {}
			if (conn != null) try { conn.close(); } catch (Exception ex) {}
		}		
		
	}

	public void updateBook(Book book) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE book " +
				"SET title = ?, publisher = ?, author = ?, pubdate = ? " + 
				"WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, book.getTitle());
			pstmt.setString(2, book.getPublisher());
			pstmt.setString(3, book.getAuthor());
			pstmt.setDate(4, book.getPubDate());
			pstmt.setInt(5, book.getBookNo());
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
	
	public void rentBook(int bookno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE book " +
				"SET status = ? " + 
				"WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, true);
			pstmt.setInt(2, bookno);
			
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
	
	public void reverseBook(int bookno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"UPDATE book " +
				"SET status = ? " + 
				"WHERE bookno = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, false);
			pstmt.setInt(2, bookno);
			
			
			pstmt.executeUpdate();
		} catch (Exception ex) {			
			ex.printStackTrace();
		} finally {
			try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { }
			try { if (conn != null) conn.close(); } catch (Exception ex) { }
		}		
	}
}
